﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Web;
using DotNet.Common;
using System.Collections;
namespace DotNet.Data.Factory
{
    /// <summary>
    /// Sql Server数据库访问类
    /// </summary>
    public class SqlHelp : IDBHelp,IDisposable
    {
        #region 定义全局 数据源
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["Zrnet_SqlConnString"].ToString();
        private SqlCommand cmd = null;
        private SqlConnection Conn = null;
        private SqlDataAdapter da = null;
        bool bShowSql = IsShowSql();
        /// <summary>
        /// 错误指示中是否显示SQL语句
        /// </summary>
        /// <returns></returns>
        private static bool IsShowSql()
        {
            bool b = false;
            if (ConfigurationManager.AppSettings["Zrnet_ShowSql"] != "")
            {
                b = Convert.ToBoolean(ConfigurationManager.AppSettings["Zrnet_ShowSql"]);
            }
            return b;
        }
        #endregion

        #region 根据SQL语句执行查询
        public  DataTable GetDataTable(string commandText)
        {
            DataTable dt = new DataTable();
            try
            {
                Conn = new SqlConnection(connectionString);
                OpenConnection();
                cmd = CreateCommd(Conn, null, commandText, null);
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                dt.Dispose();
                CloseConnection();
            }
            return dt;
        }
        #endregion

        #region 根据有参数的SQL进行查询
        public  DataTable GetDataTable(string commandText, Hashtable ht)
        {
            DataTable dt = new DataTable();
            try
            {
                Conn = new SqlConnection(connectionString);
                OpenConnection();
                cmd = CreateCommd(Conn, null, commandText, ht);
                da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                dt.Dispose();
                CloseConnection();
            }
            return dt;
        }
        #endregion

        #region 根据SQL查询，返回类型DataSet
        public  DataSet GetDataSet(string commandText)
        { 
            DataSet ds = new DataSet();
            try
            {
                ds.Tables.Add(GetDataTable(commandText));
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                ds.Dispose();
            } 
            return ds;
        }
        #endregion

        #region 根据有参数的SQL进行查询，返回类型DataSet
        public  DataSet GetDataSet(string commandText, Hashtable ht)
        {
            DataSet ds = new DataSet();
            try
            {
                ds.Tables.Add(GetDataTable(commandText, ht));
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                ds.Dispose();
            }
            return ds;
        }
        #endregion

        #region 获得连接字符串
        public string GetConnString()
        {
            return ConfigurationManager.ConnectionStrings["Zrnet_SqlConnString"].ToString();
        }
        #endregion

        #region 根据SQL查询，返回数据集中第一行第一列，返回类型objec
        public  Object ExecuteScalar(string commandText)
        {
            object ob = null;
            try
            {
                Conn = new SqlConnection(connectionString);
                OpenConnection();
                cmd = CreateCommd(Conn, null, commandText, null);
                ob = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                CloseConnection();
            }
            return ob;

        }
        #endregion

        #region 根据有参数的SQL进行查询，返回数据集中第一行第一列，返回类型objec
        public  Object ExecuteScalar(string commandText, Hashtable ht)
        {
            object ob=null;
            try
            {
                Conn = new SqlConnection(connectionString);
                OpenConnection();
                cmd = CreateCommd(Conn, null, commandText, ht);
                ob = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                CloseConnection();
            }
            return ob;
        }
        #endregion

        #region 根据SQL查询，返回执行成功行数
        public  int ExecuteNonQuery(string commandText)
        {
            int i = 0;
            try
            {
                Conn = new SqlConnection(connectionString);
                OpenConnection();
                cmd = CreateCommd(Conn, null, commandText, null);
                i = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                cmd.Dispose();

                CloseConnection();
            } 
            return i;
        }
        #endregion

        #region
        public  int ExecuteNonQuery(string commandText, Hashtable ht)
        {
                Conn = new SqlConnection(connectionString);
                OpenConnection();
                SqlTransaction trans = Conn.BeginTransaction();
                int val = 0;
                try
                {
                    cmd = CreateCommd(Conn, trans, commandText, ht);
                    val = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    string tsql = "";
                    if (bShowSql)
                        tsql = commandText;
                    throw new Exception(tsql + " " + ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    trans.Dispose();
                    CloseConnection();

                }
            return val;
        }
        #endregion

        #region
        public  bool IsExists(string commandText)
        {
            bool ret = false;
            try
            {
                object obj = ExecuteScalar(commandText);
                if (obj != null && obj.ToString() != "0")
                {
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                
            }
            return ret;
        }
        #endregion

        #region
        public  bool IsExists(string commandText, Hashtable ht)
        {
            bool ret = false;
            try
            {
                object obj = ExecuteScalar(commandText, ht);
                if (obj != null && obj.ToString() != "0")
                {
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = commandText;
                throw new Exception(tsql + " " + ex.Message);
            }
            finally
            {
                
            }
            return ret;

        }
        #endregion

        #region
        public  int GetRecordCount(string commandText)
        {
            object o = ExecuteScalar(commandText);
            if (o != null)
            {
                return int.Parse(o.ToString());
            }
            else
            {
                return 0;
            }
        }
        #endregion

        #region
        public  int GetRecordCount(string commandText, Hashtable ht)
        {
            object o = ExecuteScalar(commandText,ht);
            if (o != null)
            {
                return int.Parse(o.ToString());
            }
            else
            {
                return 0;
            }
        }
        #endregion

        #region
        public  IList<string> GetList(string commandText)
        {
            DataTable dt = GetDataTable(commandText);
            List<string> il = new List<string>();
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows[0].ItemArray.Length; i++)
                        il.Add(dt.Rows[0].ItemArray.GetValue(i).ToString());
                }
                return il;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region
        public  IList<string> GetList(string commandText, Hashtable ht)
        {
            DataTable dt = GetDataTable(commandText,ht);
            List<string> il = new List<string>();
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows[0].ItemArray.Length; i++)
                        il.Add(dt.Rows[0].ItemArray.GetValue(i).ToString());
                }
                return il;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region
        /// <summary>
        /// 获取分页数据表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="keyField"></param>
        /// <param name="fields"></param>
        /// <param name="tableName"></param>
        /// <param name="conditions"></param>
        /// <param name="orderField"></param>
        /// <param name="isDesc"></param>
        /// <returns></returns>
        public  DataTable GetPageDataTable(int pageIndex, int pageSize, string fields, string tableName, string conditions, string orderField, bool isDesc)
        {
            string sql = "";
            try
            {
                int recordCount = 0;
                object objRecordCount = ExecuteScalar("select count(1) from " + tableName + " where 1 > 0 " + conditions + "");
                if (objRecordCount != null)
                {
                    recordCount = int.Parse(objRecordCount.ToString());
                }
                int lastPageSize = recordCount % pageSize == 0 ? pageSize : (recordCount % pageSize);
                string o1, o2;
                if (isDesc)
                {
                    o1 = "desc";
                    o2 = "asc";
                }
                else
                {
                    o1 = "asc";
                    o2 = "desc";
                }
                int pageCount = recordCount % pageSize == 0 ? (recordCount / pageSize) : (CommUtil.GetInt(recordCount / pageSize) + 1);
                if (pageIndex <= 1)
                {
                    sql = "select top " + pageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " " + o1;
                }
                else
                {
                    if (pageIndex >= pageCount)
                    {
                        sql = "select * from (select top " + lastPageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " " + o2 + ")table1 order by " + orderField + " " + o1;
                    }
                    else
                    {
                        sql = "select * from (select top " + pageSize + " * from (select top " + pageIndex * pageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " " + o1 + ")table1 order by " + orderField + " " + o2 + ")table2 order by " + orderField + " " + o1;
                    }
                }
                DataTable dt = GetDataTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = sql;
                throw new Exception(tsql + " " + ex.Message);
            }
        }
        #endregion

        #region
        /// <summary>
        ///  大数据量快速分页,50万以上数据分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="fields"></param>
        /// <param name="tableName"></param>
        /// <param name="conditions"></param>
        /// <param name="orderField"></param>
        /// <param name="isDesc"></param>
        /// <returns></returns>
        public  DataTable GetMPageDataTable(int pageIndex, int pageSize, string fields, string tableName, string conditions, string orderField, bool isDesc)
        {
            string sql = "";
            try
            {
                if (isDesc)
                {
                    if (pageIndex <= 1)
                    {
                        sql = "select top " + pageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " desc";
                    }
                    else
                        sql = "Select Top " + pageSize + " " + fields + " From " + tableName + " Where " + orderField + "<(Select Min(" + orderField + ") From (Select Top " + (pageIndex - 1) * pageSize + " " + orderField + " From " + tableName + " where 1=1 " + conditions + " Order By " + orderField + " desc) T)"+ conditions +" Order By " + orderField + " desc ";     
                    
                }
                else
                {
                    if (pageIndex <= 1)
                    {
                        sql = "select top " + pageSize + " " + fields + " from " + tableName + " where 1 > 0 " + conditions + " order by " + orderField + " asc";
                    }
                    else
                        sql = "Select Top " + pageSize + " " + fields + " From " + tableName + " Where " + orderField + ">(Select Max(" + orderField + ") From (Select Top " + (pageIndex - 1) * pageSize + " " + orderField + " From " + tableName + " where 1=1 " + conditions + " Order By " + orderField + " Asc) T)" + conditions + "  Order By " + orderField + " Asc";
                    
                }

                DataTable dt = GetDataTable(sql);
                return dt;
            }
            catch (Exception ex)
            {
                string tsql = "";
                if (bShowSql)
                    tsql = sql;
                throw new Exception(tsql + " " + ex.Message);
            }
        }
        #endregion

        #region 装载命令
        /// <summary>
        /// 整合ACC和MSSQL数据库参数差异化配置
        /// </summary>
        /// <param name="sqlConn">数据库连接对象</param>
        /// <param name="transaction">事务对象</param>
        /// <param name="sqlStr">执行的SQL语句</param>
        /// <param name="ht">HASH表</param>
        /// <returns></returns>
        public SqlCommand CreateCommd(SqlConnection Conn, SqlTransaction transaction, string sqlStr, Hashtable ht)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Conn;
            cmd.CommandText = sqlStr;
            cmd.CommandType = CommandType.Text;
            if (transaction != null)
            {
                cmd.Transaction = transaction;
            }
            if (ht!=null)
            {
                SqlParameter[] param = new SqlParameter[ht.Count];
                int i = 0;
                foreach (DictionaryEntry objDE in ht)
                {
                    param[i] = new SqlParameter(objDE.Key.ToString(), null);
                    if (objDE.Value == null)
                    {
                        param[i++].Value = DBNull.Value;
                    }
                    else
                        param[i++].Value = objDE.Value;
                }
                cmd.Parameters.AddRange(param);
            }
            //OpenConnection();
            return cmd;
        }
        public void OpenConnection()
        {
            if (Conn.State == ConnectionState.Broken)
            {
                 Conn.Close();
                 Conn.Open();
            }
            else if (Conn.State == ConnectionState.Closed)
            {
                Conn.Open();
            }
            else
            {
                Conn.Open();
            }
        }
        public void CloseConnection()
        {
            if(Conn!=null)
            Conn.Close();
        }
        #endregion
      
        #region IDisposable 成员

        public void Dispose()
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}
